package manager.modulo.financeiro.dao;

import java.sql.SQLException;
import manager.auxiliares.MensagemExcessao;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.LinkedList;
import manager.auxiliares.Configurations;
import manager.auxiliares.Format;
import manager.principal.fonte.JanelaLogin;
import manager.interfaces.InterfaceDao;

/**
 * Esta classe será utilizada para cadastra a entidade user no banco de dados
 *
 * @author Thiago
 */
public class SecondaryCategoryDao implements InterfaceDao {

    int id;
    String name;
    int situation;
    int idFinancialCategory;

    /**
     * Inicializando as variáveis
     */
    public SecondaryCategoryDao() {
        name = "";
        id = 0;
        situation = 0;
        idFinancialCategory = 0;
    }

    /**
     * Buscar o id de uma cidade pelo nome da mesma
     *
     * @param name Nome da cidade a ser buscada
     * @return Id da da cidade encontrata, 0 quando não encontra
     */
    public static int getIdSecondaryCategoryByName(String name) {
        try {

            String sql = "SELECT id FROM secondary_category WHERE name = ?";
            PreparedStatement preparedStatement = JanelaLogin.getConnection().prepareStatement(sql);
            preparedStatement.setString(1, name);
            ResultSet resultSet = preparedStatement.executeQuery();
            int idCity = 0;
            if (resultSet.first()) {
                idCity = resultSet.getInt("id");
            }
            resultSet.close();
            preparedStatement.close();
            return idCity;
        } catch (Exception e) {
            MensagemExcessao.mostrarMensagem(e, "Buscando Subcategorias");
            return 0;
        }
    }

    public LinkedList<String> buscar(String option, String search, boolean limite) throws SQLException {
        LinkedList<String> result = new LinkedList<String>();
        if (!search.equals("")) {
            String sql = "";
            String tamanhoBusca = limite ? ("LIMIT 0," + Configurations.getAutoCompleteSizeSearch()) : "";
            if (option.equalsIgnoreCase("Nome")) {// Buscando por Email
                sql = "SELECT name FROM secondary_category WHERE name LIKE ? ORDER BY name " + tamanhoBusca;
            }
            if (!sql.equals("")) {
                PreparedStatement preparedStatement = JanelaLogin.getConnection().prepareStatement(sql);
                preparedStatement.setString(1, search + "%");
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    result.add(resultSet.getString(1));
                }
            }
        }
        return result;
    }

    public static LinkedList searchTable(int idFanancialCategory) {
        LinkedList result = new LinkedList();
        try {
            String sql = "SELECT * FROM secondary_category WHERE id_financial_category = ? ORDER BY name";

            PreparedStatement preparedStatement = JanelaLogin.getConnection().prepareStatement(sql);
            preparedStatement.setInt(1, idFanancialCategory);
            ResultSet resultSet = preparedStatement.executeQuery();

            LinkedList<Integer> columnId = new LinkedList<Integer>();
            LinkedList<String> columnName = new LinkedList<String>();
            LinkedList<String> columnSituation = new LinkedList<String>();

            while (resultSet.next()) {
                columnId.add(resultSet.getInt("id"));
                columnName.add(resultSet.getString("name"));
                columnSituation.add(getSituation(resultSet.getInt("situation")));
            }

            result.add(columnId);
            result.add(Format.getBooleanLinkedList(columnId.size()));
            result.add(columnName);
            result.add(columnSituation);
        } catch (Exception e) {
            MensagemExcessao.mostrarMensagem(e, "Buscando Subcategorias");
        }
        return result;
    }

    /**
     * Carregar as informações do objeto usuário a partir de um id
     *
     * @param id Id do usuário buscado
     */
    public void buscar(int id) {
        try {
            setId(id);
            String sql = "SELECT * FROM secondary_category WHERE id = ?";
            PreparedStatement preparedStatement = JanelaLogin.getConnection().prepareStatement(sql);
            preparedStatement.setInt(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.first()) {
                name = resultSet.getString("name");
                idFinancialCategory = resultSet.getInt("id_financial_category");
                situation = resultSet.getInt("situation");
            }
            resultSet.close();
            preparedStatement.close();
        } catch (Exception e) {
            MensagemExcessao.mostrarMensagem(e, "Buscando Subcategorias");
        }
    }

    /**
     * Insere um usuário no banco
     */
    public void inserir() throws SQLException {
        String sql = "INSERT INTO secondary_category (name,id_financial_category,situation) VALUES(?,?,?)";
        PreparedStatement preparedStatement = JanelaLogin.getConnection().prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);

        preparedStatement.setString(1, name);
        preparedStatement.setInt(2, idFinancialCategory);
        preparedStatement.setInt(3, situation);
        preparedStatement.executeUpdate();

        //Recebendo o novo id gerado
        ResultSet resultSet = preparedStatement.getGeneratedKeys();
        while (resultSet.next()) {
            id = resultSet.getInt(1);
        }

        preparedStatement.close();
        resultSet.close();
    }

    /**
     * Atualiza o usuário no banco
     */
    public void atualizar() throws SQLException {
        String sql = "UPDATE secondary_category SET name= ?, situation = ?, id_financial_category = ? WHERE id = ?";
        PreparedStatement preparedStatement = JanelaLogin.getConnection().prepareStatement(sql);
        preparedStatement.setString(1, name);
        preparedStatement.setInt(2, situation);
        preparedStatement.setInt(3, idFinancialCategory);
        preparedStatement.setInt(4, id);
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }

    public static String getSituation(int currentSituation) {
        switch (currentSituation) {
            case 1:
                return "ATIVADO";
            default:
                return "DESATIVADO";
        }
    }

    public static int getSituation(String currentSituation) {
        if (currentSituation.equalsIgnoreCase("ATIVADO")) {
            return 1;
        } else {
            return 0;
        }
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getIdFinancialCategory() {
        return idFinancialCategory;
    }

    public void setIdFinancialCategory(int idFinancialCategory) {
        this.idFinancialCategory = idFinancialCategory;
    }

    public int getSituation() {
        return situation;
    }

    public void setSituation(int situation) {
        this.situation = situation;
    }

    public void deletar() {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
